using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace MonoCash.Data
{
    public class DataBackend
    {
        public DataBackend()
        {
            MonoCash.Tables.AddRange(GetDataTables());
            try
            {
                initiateTables();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + "\n" + ex.StackTrace);
            }
        }
        public DataSet MonoCash = new DataSet("MonoCash");
        public DataTable TransactionDetailTypes = new DataTable("TransactionDetailTypes");
        public DataTable AccountingCategories = new DataTable("AccountingCategories");
        public DataTable AccountTypes = new DataTable("AccountTypes");
        public DataTable Accounts = new DataTable("Accounts");
        public DataTable Currencies = new DataTable("Currencies");
        public DataTable CurrencyConversions = new DataTable("CurrencyConversions");
        public DataTable Securities = new DataTable("Securities");
        public DataTable Prices = new DataTable("Prices");       
        public DataTable Transactions = new DataTable("Transactions");
        public DataTable TransactionDetails = new DataTable("TransactionDetails");
        public DataTable ExtendedProperties = new DataTable("ExtendedProperties");
        public bool IsDataLoaded = false;
        protected DataTable[] GetDataTables()
        {
            List<DataTable> temp = new List<DataTable>();
            foreach (System.Reflection.FieldInfo fi in GetType().GetFields())
            {
                if (fi.FieldType.Name == "DataTable") 
                {
                    temp.Add((DataTable)fi.GetValue(this));
                }
            }
            return temp.ToArray();
        }
        public DataTable GetDataTable(string tableName)
        {
            DataTable temp = new DataTable();
            foreach (System.Reflection.FieldInfo fi in GetType().GetFields())
            {
                if (fi.FieldType.Name == "DataTable" && fi.Name == tableName)
                {
                    temp = (DataTable)fi.GetValue(this);
                }
            }
            return temp;
        }
        public virtual void LoadData()
        {
            //Program.AppSettings.Data.LoadData();
        }
        public virtual void SaveData()
        {
            //Program.AppSettings.Data.SaveData();
        }
        public int GetPrices()
        {
            return Program.AppSettings.PriceEngine.GetQuotes();
        }
        public int GetHistoricalPrices()
        {
            return Program.AppSettings.PriceEngine.RefreshHistory();
        }
        private void initiateTables()
        {
            Securities.Columns.Add("ID", Type.GetType("System.Int32"));
            Securities.Columns["ID"].AutoIncrement = true;
            Securities.Columns["ID"].AutoIncrementSeed = 1;
            Securities.Columns.Add("Symbol", Type.GetType("System.String"));
            Securities.Columns.Add("Description", Type.GetType("System.String"));
            Securities.Columns.Add("CurrencyID", Type.GetType("System.Int32"));
            Securities.Columns.Add("DownloadPrices", Type.GetType("System.Boolean"));
            Securities.Columns.Add("ValuationFactor", Type.GetType("System.Decimal"));

            Securities.PrimaryKey = new DataColumn[] { Securities.Columns["ID"] };

            Currencies.Columns.Add("ID", Type.GetType("System.Int32"));
            Currencies.Columns["ID"].AutoIncrement = true;
            Currencies.Columns["ID"].AutoIncrementSeed = 1;
            Currencies.Columns.Add("Symbol", Type.GetType("System.String"));
            Currencies.Columns.Add("Description", Type.GetType("System.String"));

            Currencies.PrimaryKey = new DataColumn[] { Currencies.Columns["ID"] };

            CurrencyConversions.Columns.Add("CurrencyID1", Type.GetType("System.Int32"));
            CurrencyConversions.Columns.Add("CurrencyID2", Type.GetType("System.Int32"));
            CurrencyConversions.Columns.Add("Date", Type.GetType("System.DateTime"));
            CurrencyConversions.Columns.Add("Rate", Type.GetType("System.Decimal"));

            CurrencyConversions.PrimaryKey = new DataColumn[] { CurrencyConversions.Columns["Symbol1"], CurrencyConversions.Columns["Symbol2"], CurrencyConversions.Columns["Date"] };

            Prices.Columns.Add("ID", Type.GetType("System.Int32"));
            Prices.Columns["ID"].AutoIncrement = true;
            Prices.Columns["ID"].AutoIncrementSeed = 1;
            Prices.Columns.Add("Date", Type.GetType("System.DateTime"));
            Prices.Columns.Add("SecurityID", Type.GetType("System.Int32"));
            Prices.Columns.Add("LastPrice", Type.GetType("System.Decimal"));
            Prices.Columns.Add("EntryDate", Type.GetType("System.DateTime"));

            Prices.PrimaryKey = new DataColumn[] { Prices.Columns["ID"] };

            Accounts.Columns.Add("ID", Type.GetType("System.Int32"));
            Accounts.Columns["ID"].AutoIncrement = true;
            Accounts.Columns["ID"].AutoIncrementSeed = 1;
            Accounts.Columns.Add("Name", Type.GetType("System.String"));
            Accounts.Columns.Add("AccountTypeID", Type.GetType("System.Int32"));
            Accounts.Columns.Add("AccountingCategoryID", Type.GetType("System.Int32"));
            Accounts.Columns.Add("ParentAccountID", Type.GetType("System.Int32"));
            Accounts.Columns.Add("AllowTransactions", Type.GetType("System.Boolean"));
            Accounts.Columns.Add("DisplayOrder", Type.GetType("System.Int32"));
            Accounts.Columns.Add("IsVisible", Type.GetType("System.Boolean"));
            
            Accounts.PrimaryKey = new DataColumn[] { Accounts.Columns["ID"] };

            AccountTypes.Columns.Add("ID", Type.GetType("System.Int32"));
            AccountTypes.Columns["ID"].AutoIncrement = true;
            AccountTypes.Columns["ID"].AutoIncrementSeed = 1;
            AccountTypes.Columns.Add("Name", Type.GetType("System.String"));
            
            AccountTypes.PrimaryKey = new DataColumn[] { AccountTypes.Columns["ID"] };

            AccountingCategories.Columns.Add("ID", Type.GetType("System.Int32"));
            AccountingCategories.Columns["ID"].AutoIncrement = true;
            AccountingCategories.Columns["ID"].AutoIncrementSeed = 1;
            AccountingCategories.Columns.Add("Name", Type.GetType("System.String"));
            AccountingCategories.Columns.Add("ReverseSignForDisplay", Type.GetType("System.Boolean"));
            AccountingCategories.Columns.Add("IsBalanceSheetCategory", Type.GetType("System.Boolean"));

            AccountingCategories.PrimaryKey = new DataColumn[] { AccountingCategories.Columns["ID"] };

            Transactions.Columns.Add("ID", Type.GetType("System.Int32"));
            Transactions.Columns["ID"].AutoIncrement = true;
            Transactions.Columns["ID"].AutoIncrementSeed = 1;
            Transactions.Columns.Add("Payee", Type.GetType("System.String"));
            Transactions.Columns.Add("Memo", Type.GetType("System.String"));
            Transactions.Columns.Add("Date", Type.GetType("System.DateTime"));
            Transactions.Columns.Add("EntryDate", Type.GetType("System.DateTime"));

            Transactions.PrimaryKey = new DataColumn[] { Transactions.Columns["ID"] };

            TransactionDetails.Columns.Add("ID", Type.GetType("System.Int32"));
            TransactionDetails.Columns["ID"].AutoIncrement = true;
            TransactionDetails.Columns["ID"].AutoIncrementSeed = 1;
            TransactionDetails.Columns.Add("TransactionID", Type.GetType("System.Int32"));
            TransactionDetails.Columns.Add("AccountID", Type.GetType("System.Int32"));
            TransactionDetails.Columns.Add("SecurityID", Type.GetType("System.Int32"));
            TransactionDetails.Columns.Add("TransactionDetailTypeID", Type.GetType("System.Int32"));
            TransactionDetails.Columns.Add("Quantity", Type.GetType("System.Decimal"));
            TransactionDetails.Columns.Add("FxRate", Type.GetType("System.Decimal"));
            TransactionDetails.Columns.Add("Amount", Type.GetType("System.Decimal"));

            TransactionDetails.RowDeleted += new DataRowChangeEventHandler(TransactionDetails_RowChanged);
            TransactionDetails.RowChanged += new DataRowChangeEventHandler(TransactionDetails_RowChanged);  

            TransactionDetails.PrimaryKey = new DataColumn[] { TransactionDetails.Columns["ID"], TransactionDetails.Columns["TransactionID"] };

            TransactionDetailTypes.Columns.Add("ID", Type.GetType("System.Int32"));
            TransactionDetailTypes.Columns["ID"].AutoIncrement = true;
            TransactionDetailTypes.Columns["ID"].AutoIncrementSeed = 1;
            TransactionDetailTypes.Columns.Add("Name", Type.GetType("System.String"));
            TransactionDetailTypes.Columns.Add("IncludeInCostBasis", Type.GetType("System.Boolean"));

            TransactionDetailTypes.PrimaryKey = new DataColumn[] {TransactionDetailTypes.Columns["ID"]};

            ExtendedProperties.Columns.Add("ID", Type.GetType("System.Int32"));
            ExtendedProperties.Columns["ID"].AutoIncrement = true;
            ExtendedProperties.Columns["ID"].AutoIncrementSeed = 1;
            ExtendedProperties.Columns.Add("TableName", Type.GetType("System.String"));
            ExtendedProperties.Columns.Add("TableRow", Type.GetType("System.Int32"));
            ExtendedProperties.Columns.Add("Name", Type.GetType("System.String"));
            ExtendedProperties.Columns.Add("Value", Type.GetType("System.String"));

            ExtendedProperties.PrimaryKey = new DataColumn[] { ExtendedProperties.Columns["ID"] };
            ExtendedProperties.Constraints.Add(new UniqueConstraint(new DataColumn[] { ExtendedProperties.Columns["TableName"], ExtendedProperties.Columns["TableRow"], ExtendedProperties.Columns["Name"] }));

            foreach (DataTable table in MonoCash.Tables)
            {
                //For constraining to ExtendedProperties
                if (table.TableName != "ExtendedProperties" && table.Columns.Contains("ID"))
                {
                    table.Columns.Add("TableName", Type.GetType("System.String"));
                    table.Columns["TableName"].ReadOnly = true;
                    table.Columns["TableName"].DefaultValue = table.TableName;
                    
                    try
                    {  
                        DataRelation r = new DataRelation(
                            table.TableName + "_ExtendedProperties",
                            new DataColumn[] { table.Columns["TableName"], table.Columns["ID"] },
                            new DataColumn[] { ExtendedProperties.Columns["TableName"], ExtendedProperties.Columns["TableRow"] },
                            true);
                        table.ChildRelations.Add(r);
                    }
                    catch { }
                }


                foreach (DataColumn col in table.Columns)
                {

                    int systemCurrencyId = int.Parse(Program.AppSettings.DefaultCurrencyID);
                    int defaultSecurityId = int.Parse(Program.AppSettings.DefaultSecurityID);
                    int defaultTransactionDetailType = int.Parse(Program.AppSettings.DefaultTransactionDetailType);
                    int orphanAccountId = int.Parse(Program.AppSettings.UnbalancedTransactionsAccountID);


                    if (col.ColumnName.EndsWith("ID") && col.ColumnName.Length > 2 )
                    {
                        //Set relations
                        string parentTableName = col.ColumnName.Replace("ID", "");
                        parentTableName = parentTableName.Replace("Parent", "");
                        if (parentTableName.EndsWith("y"))
                        {
                            parentTableName = parentTableName.Substring(0, parentTableName.Length - 1) + "ie";
                        }
                        parentTableName += "s";
                        DataTable parent = GetDataTable(parentTableName);

                        parent.ChildRelations.Add(parent.TableName + "_" + table.TableName, parent.Columns["ID"], col, !col.ColumnName.Contains("Parent"));

                        //Defaults from settings
                        switch (col.ColumnName)
                        {
                            case "CurrencyID": 
                                col.DefaultValue = systemCurrencyId;
                                break;
                            case "SecurityID":
                                col.DefaultValue = defaultSecurityId;
                                break;
                            case "AccountID":
                                col.DefaultValue = orphanAccountId;
                                break;
                            case "TransactionDetailTypeID":
                                col.DefaultValue = defaultTransactionDetailType;
                                break;
                            default:
                                col.DefaultValue = -1;
                                break;
                        }
                        
                    }
                }

                foreach (Constraint c in table.Constraints)
                {
                    if (c.ConstraintName != "Accounts_Accounts" && !c.ConstraintName.Contains("ExtendedProperties"))
                    {
                        if (c.GetType().Name == "ForeignKeyConstraint")
                        {
                            ForeignKeyConstraint f = (ForeignKeyConstraint)c;
                            f.DeleteRule = Rule.None;
                        }
                    }
                }
            }

        }


        /// <summary>
        /// Automatically generates orphan transactions on add/change/delete
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void TransactionDetails_RowChanged(object sender, DataRowChangeEventArgs e)
        {
            //if (IsDataLoaded)
            //{
            //    string computation = "Sum(Child(Transactions_TransactionDetails).Amount) ";
            //    DataRow[] rowsToUnOrphan = Transactions.Select(computation + " <> 0");
            //    foreach (DataRow orphanRow in rowsToUnOrphan)
            //    {
                    
            //        Decimal amount = -1 * Decimal.Parse(TransactionDetails.Compute("Sum(Amount)", "TransactionID= " + orphanRow["ID"]).ToString());
            //        ObjectModel.TransactionDetail td = new MonoCash.ObjectModel.TransactionDetail();
                    
            //        td.Amount = amount;
            //        td.TransactionID = int.Parse(orphanRow["ID"].ToString()); ;

            //    }
            //}
            
        }

        public Decimal GetConversionRate(object translateFromCurrencyID, object translateToCurrencyId, object date)
        {
            return GetConversionRate(Convert.ToInt32(translateFromCurrencyID), Convert.ToInt32(translateToCurrencyId), Convert.ToDateTime(date));
        }
        public Decimal GetConversionRate(int translateFromCurrencyID, int translateToCurrencyId, DateTime date)
        {
            Decimal temp = 1;
            try
            {
                string filter = "Date <= '" + date.ToLongDateString() + "' and CurrencyID1=" + translateFromCurrencyID.ToString() + " and CurrencyID2=" + translateToCurrencyId.ToString();

                DateTime bestDate = (DateTime)Program.AppSettings.Data.CurrencyConversions.Compute("Max(Date)", filter);
                filter = filter.Replace("<=", "=");
                filter = filter.Replace(date.ToLongDateString(), bestDate.ToLongDateString());

                temp = (Decimal)Program.AppSettings.Data.Prices.Select(filter)[0]["Rate"];
            }
            catch (Exception ex) 
            { 
                Console.WriteLine(ex.Message); 
            }
            return temp;
        }
        public Boolean AccountAllowsTransactions(int accountID)
        {
            bool temp = false;
            try
            {
                temp = Convert.ToBoolean(Accounts.Select("ID = " + accountID)[0]["AllowTransactions"]);
            }
            catch { }
            return temp;
        }
    }
}
